﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Timers;
using System.Linq;

using CE.Mail;
using CE.InputOut;
using System.Collections;
using CE.Database;
using CE.CarInsurance;
using System.Net;
namespace GG_Re_Run_Quote_Service
{
    partial class SendReRunQuote : ServiceBase
    {
        Int64 _carId = 0;
        Int64 _userId = 0;
        Int64 _logId = 0;

        EventLog _eventLog = new EventLog();

        private int _emailIntervalSec = Convert.ToInt32(System.Configuration.ConfigurationSettings.AppSettings["EmailIntervalSec"].ToString());
        private int _timerNormalInterval = 10;
        string _serviceTitle = "GG Re-run Quote Mail Service";

        public SendReRunQuote()
        {
            InitializeComponent();
            if (!System.Diagnostics.EventLog.SourceExists("GG_Rerun_Quote_MailService"))
                System.Diagnostics.EventLog.CreateEventSource("GG_Rerun_Quote_MailService",
                                                                      _serviceTitle);
            _eventLog.Source = "GG_Rerun_Quote_MailService";
        }

        protected override void OnStart(string[] args)
        {
            _eventLog.WriteEntry(_serviceTitle + " started " + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt"), EventLogEntryType.Information);
            timer1.Interval = _timerNormalInterval * 1000;
            timer1.Enabled = true;
        }

        protected override void OnStop()
        {
            timer1.Enabled = false;
            _eventLog.WriteEntry(_serviceTitle + " stopped " + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt"), EventLogEntryType.Information);
            //_eventLog.WriteEntry("CX - Policy Listing OnStop", EventLogEntryType.Information);
        }

        private void Timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
        //    StartSendingEmail();
        //}

        //public void StartService()
        //{
        //    StartSendingEmail();
        //    //timer1.Enabled = false;
        //}
        //private void StartSendingEmail()
        //{

            //timer1.Enabled = false;
            int NoOfDays = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["NoOfDays"].ToString());
            //timer1.Interval = (24 * NoOfDays) * 60 * 60 * 1000;
            timer1.Enabled = false;

            string _conString = "";
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();

            string _emailTitle = string.Empty;
            string[] fileList = new string[0];
            string _toemail = "";
            string _ccemail = "";
            string _bccemail = "";
            StringBuilder _emailBody;
            System.Text.StringBuilder _strEmailList = new StringBuilder();
            string _step = "Step 1";
            int _mailOneSendCount = 0;
            try
            {
                _eventLog.WriteEntry(_step + " " + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt"), EventLogEntryType.Information);
                string IsLive = System.Configuration.ConfigurationManager.AppSettings["IsOnLive"].ToString();
                string IsSendEmailCustomer = System.Configuration.ConfigurationManager.AppSettings["IsSendEmailCustomer"].ToString();
                //NoOfDays = 1 = today
                _toemail = System.Configuration.ConfigurationSettings.AppSettings["ToEmail"].ToString();
                _ccemail = System.Configuration.ConfigurationSettings.AppSettings["CcEmail"].ToString();
                _bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccEmail"].ToString();

                string _smtpAccount = System.Configuration.ConfigurationManager.AppSettings["SMTPAccount"].ToString();
                string _smtpAuthCode = System.Configuration.ConfigurationManager.AppSettings["SMTPAuthCode"].ToString();
                string _smtpInternalAccount = System.Configuration.ConfigurationManager.AppSettings["InternalSMTPAccount"].ToString();
                string _smtpInternalAuthCode = System.Configuration.ConfigurationManager.AppSettings["InternalSMTPAuthCode"].ToString();
                string _smtpReplyTo = System.Configuration.ConfigurationManager.AppSettings["SMTPReplyTo"].ToString();

                _step = "Step 2";
                _eventLog.WriteEntry(_step + " " + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt"), EventLogEntryType.Information);
                _conString = ConfigurationManager.ConnectionStrings["CX_DBConnectionString"].ToString();


                _sqlConn.ConnectionString = _conString;
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.StoredProcedure;
                _sqlCmd.CommandText = "sprocGGReRunQuoteUserList";
                _sqlConn.Open();
                _sqlCmd.ExecuteNonQuery();
                _sqlConn.Close();

                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.StoredProcedure;
                _sqlCmd.CommandText = "sprocGGReRunQuoteUserList";
                _sqlConn.Open();
                _sqlCmd.ExecuteNonQuery();
                _sqlConn.Close();

                DataSet _dsResult = new DataSet();
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.StoredProcedure;
                _sqlCmd.CommandText = "sprocGGReRunQuoteUserList";
                //SqlParameter _p1 = new SqlParameter("@NoOfDays", NoOfDays);
                //_sqlCmd.Parameters.Add(_p1);

                SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);

                _sqlDa.Fill(_dsResult);


                _step = "Step 3";
                _eventLog.WriteEntry(_step + " " + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt"), EventLogEntryType.Information);

                #region MailBody


                if (_dsResult != null && _dsResult.Tables.Count > 0)
                {
                    _step = "Step 4";
                    int _mailThreeSendCount = 0;
                    int _noQuotationResultCount = 0;
                    //StringBuilder strNoQuotationCarList = new StringBuilder();
                    //strNoQuotationCarList.Append("<table width=\"300px\" border=\"1\" cellspacing=\"5\" cellpadding=\"5\"><tr><th><b>Car ID</b></th><th><b>Customer ID</b></th></tr>");
                    if (_dsResult.Tables[0].Rows.Count > 0)
                    {
                        _eventLog.WriteEntry(_serviceTitle + " Started at [" + MyMailManager.GetUTCDateTime().ToString("dd/MM/yyyy hh:mm:ss") + "].", EventLogEntryType.Information);
                    }
                    else
                    {
                        _eventLog.WriteEntry(_serviceTitle + " No Mail to send. [" + MyMailManager.GetUTCDateTime().ToString() + "].", EventLogEntryType.Information);
                    }

                    //9/12/2556 save to tblWindowServiceLog
                    MyDatabase _dbLog = new MyDatabase();
                    string _spLogName = "[sprocWindowServiceSaveLog]";
                    SortedList _sLogPara = new SortedList();
                    _sLogPara.Add("@ServiceName", "GG_Re_Run_Quote_Service");
                    _sLogPara.Add("@Subject", _serviceTitle + " was started at " + MyMailManager.GetUTCDateTime().ToString("dd/MM/yyyy hh:mm:ss"));
                    _sLogPara.Add("@EmailCount", _dsResult.Tables[0].Rows.Count);
                    _sLogPara.Add("@ServiceStatus", 1);
                    _sLogPara.Add("@Status", 1);

                    _dbLog.ExecuteNonSqlSP(_spLogName, _sLogPara);

                    //_emailBody = new StringBuilder();
                    //_emailTitle = "<Started> " + _serviceTitle + " - " + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt");
                    //_emailBody.Append("Dear All,<br/><br/>");
                    //_emailBody.Append(_serviceTitle + " was started at " + MyMailManager.GetUTCDateTime().ToString("dd/MMM/yyyy hh:mm:ss") + "<br/><br/>");

                    ////_emailBody.Append("No. of days to batch : <b>" + NoOfDays.ToString() + "</b> days.<br/><br/>");

                    //if (_dsResult.Tables[0].Rows.Count > 0)
                    //{
                    //    _emailBody.Append("No. Re-run quote mail : <b>" + _dsResult.Tables[0].Rows.Count + "</b><br/><br/>");

                    //    //_emailBody.Append("<b>Please check following customer list details as your reference.</b><br/><br/>");

                    //    if (_dsResult.Tables[0].Rows.Count > 0)
                    //    {
                    //        _emailBody.Append("<table border='1'>");
                    //        _emailBody.Append("<caption style='color:blue'><b>Re-run Quote mail</b></caption>");
                    //        _emailBody.Append("<tr bgcolor='lightgray'><th>No.</th><th>Customer ID</th><th>Car ID</th><th>Name</th><th>Created Date</th></tr>");
                    //        for (int i = 0; i < _dsResult.Tables[0].Rows.Count; i++)
                    //        {
                    //            _emailBody.Append("<tr " + ((i % 2 == 0) ? "bgcolor='#ffffcc'" : "") + "><td>" + (i + 1).ToString() + "</td><td>" + _dsResult.Tables[0].Rows[i]["RefCEUserId"] + "</td><td>" + _dsResult.Tables[0].Rows[i]["CarID"] + "</th><td>" + _dsResult.Tables[0].Rows[i]["FirstName"] + " " + _dsResult.Tables[0].Rows[i]["LastName"] + "</td><td>" + Convert.ToDateTime(_dsResult.Tables[0].Rows[i]["CreatedQuoteDate"]).ToString("dd-MMM-yyyy") + "</td></tr>");
                    //        }
                    //        _emailBody.Append("</table>");
                    //        _emailBody.Append("<br/><br/>");
                    //    }
                    //}
                    //else
                    //{
                    //    _emailBody.Append("<b>No mail to send.</b>");
                    //}


                    //while (!MyMailManager.SendGGMail(_smtpInternalAccount, _smtpInternalAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                    //{
                    //    _eventLog.WriteEntry(_serviceTitle + " - Failed to send STARTED MAIL to GG-Team at [" + MyMailManager.GetUTCDateTime().ToString() + "].", EventLogEntryType.Error);
                    //    System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                    //}
                    //_mailOneSendCount += 1;

                    if (IsSendEmailCustomer == "1")
                    {
                        //CC and BCC with customer mail
                        string _ccemailwithcus = System.Configuration.ConfigurationSettings.AppSettings["CcWithCustomerEmail"].ToString();
                        //_bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccWithCustomerEmail"].ToString();
                        string _bccemailen = "";
                        string _bccemailth = "";
                        if (System.Configuration.ConfigurationSettings.AppSettings["IsCustomerEmailCheck"].ToString() == "1")
                        {
                            _bccemailen = System.Configuration.ConfigurationSettings.AppSettings["BccWithCustomerEmail"].ToString();
                            _bccemailth = System.Configuration.ConfigurationSettings.AppSettings["BccWithCustomerEmail"].ToString();
                        }

                        int _IsEn = 1;

                        foreach (DataRow drResrult1 in _dsResult.Tables[0].Rows)
                        {
                            _logId = Convert.ToInt64(drResrult1["LogId"]);
                            _userId = Convert.ToInt64(drResrult1["RefCEUserId"]);
                            _carId = Convert.ToInt64(drResrult1["CarId"]);
                            int resultCalculatePremium = CalculatePremium();
                            #region Edit Change Class

                            if (resultCalculatePremium == 0)
                            {
                                if (CalculateOtherClass(_carId, _userId, 21) <= 0)
                                {
                                    if (CalculateOtherClass(_carId, _userId, 31) <= 0)
                                    {
                                        CalculateOtherClass(_carId, _userId, 30);
                                    }
                                }
                            }
                            #endregion

                            MyDatabase _db = new MyDatabase();
                            //DataTable _dtResult = new DataTable();
                            string _spName = "[sprocCarInsuranceResultSelectItemList]";
                            SortedList _sPara = new SortedList();
                            //_sPara.Add("@CECarId", Session[General.CarId].ToString());
                            _sPara.Add("@CarId", _carId);
                            _sPara.Add("@RefUserId", _userId);
                            DataTable _dtPremiumList = new DataTable("tblPremiumList");
                            _dtPremiumList = _db.GetDataTableBySP(_spName, _sPara);
                            //////////////////////////////////////////////////

                            if (IsLive == "1")
                            {
                                _toemail = drResrult1["Email"].ToString();//"niran.s@comparexpress.com";// 
                                //_toemail = "kandasamy.sakthivel@comparexpress.com"; 
                            }

                            if (_toemail.Trim() != "")
                            {

                                int sentAttemptCount = 0;
                                _emailBody = new StringBuilder();
                                if ((IsLive == "1" && drResrult1["Lang"].ToString() == "th") || (IsLive == "0" && _IsEn == 1))
                                //if (_IsEn == 1)
                                {
                                    _IsEn = 0;
                                    //Thai 
                                    //StringBuilder _emailTableRow = new StringBuilder();

                                    ///cardetail//////////////CP.InsuranceType, CP.GarageType, CP.VehicleType, CP.SumInsured, CP.NCDPercent, CP.IsClaimLastYear, CP.IsCTPL,"
                                    // + " CP.IsAddDriver, CP.NoOfDriver,  CP.CoverageCommenceDate, CP.Info6, CP.Info7, CP.ModifiedDate, CD.Info3, CP.Info8 "
                                    DataSet _dsCarDetail = new DataSet();
                                    string _strSelect = "SELECT CD.MakeModelDesc, CD.ManufactYear,  CP.InsuranceType, CP.GarageType,CP.IsAddDriver "
                                                        + " FROM tblCarPolicy CP INNER JOIN tblCarDetailTH CD ON CD.CarId = CP.CECarId "
                                                        + " WHERE CP.RefCEUserId = " + _userId.ToString() + " AND CP.CECarId= " + _carId.ToString();
                                    SqlDataAdapter _daCar = new SqlDataAdapter(_strSelect, _sqlConn);
                                    _sqlConn.Open();
                                    _daCar.Fill(_dsCarDetail);
                                    _sqlConn.Close();
                                    ///////////////////////////
                                    string _insuranceType = "-";
                                    string _garageType = "";
                                    string _adddriver = "";
                                    if (_dsCarDetail.Tables[0].Rows.Count > 0)
                                    {
                                        _insuranceType = GetClassNameEN(_dsCarDetail.Tables[0].Rows[0]["InsuranceType"].ToString());
                                        _garageType = _dsCarDetail.Tables[0].Rows[0]["GarageType"].ToString() == "2" ? "ซ่อมศูนย์" : "ซ่อมอู่";
                                        _adddriver = _dsCarDetail.Tables[0].Rows[0]["IsAddDriver"].ToString() == "1" ? "ใช่" : "ไม่";
                                    }
                                    StringBuilder _emailTableRow1 = new StringBuilder();
                                    StringBuilder _emailTableRow2 = new StringBuilder();
                                    StringBuilder _emailTableRow2_2 = new StringBuilder();
                                    StringBuilder _emailTableRow3 = new StringBuilder();
                                    StringBuilder _emailTableRow4 = new StringBuilder();
                                    StringBuilder _emailTableRow5 = new StringBuilder();
                                    StringBuilder _emailTableRow6 = new StringBuilder();
                                    StringBuilder _emailTableRow7 = new StringBuilder();
                                    StringBuilder _emailTableRow8 = new StringBuilder();
                                    StringBuilder _emailTableRow9 = new StringBuilder();
                                    StringBuilder _emailTableRow10 = new StringBuilder();
                                    StringBuilder _emailTableRow11 = new StringBuilder();
                                    StringBuilder _emailTableRow12 = new StringBuilder();
                                    StringBuilder _emailTableRow13 = new StringBuilder();
                                    StringBuilder _emailTableRow14 = new StringBuilder();
                                    StringBuilder _emailTableRow15 = new StringBuilder();
                                    StringBuilder _emailTableRow16 = new StringBuilder();
                                    StringBuilder _emailTableRow17 = new StringBuilder();
                                    //StringBuilder _emailTableRow18 = new StringBuilder();
                                    //StringBuilder _emailTableRow19 = new StringBuilder();
                                    StringBuilder _emailTableRow20 = new StringBuilder();
                                    if (_dtPremiumList.Rows.Count > 0)
                                    {
                                        _emailTableRow1.Append("<tr class='table-txt'><td height='40' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: bold'>บริษัทประกันภัย</td>");
                                        _emailTableRow2.Append("<tr><td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>บริษัทประกันภัย</span></td>");
                                        _emailTableRow2_2.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>ประเภทประกันภัย</span></td>");
                                        _emailTableRow3.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>ประเภทอู่</span></td>");
                                        _emailTableRow4.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>การระบุผู้ขับขี่</span></td>");
                                        _emailTableRow5.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#FFFFFF'><span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'>ความรับผิดชอบต่อบุคคลภานนอก</span></td>");
                                        _emailTableRow6.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>บาดเจ็บหรือเสียชีวิต (ต่อคน)</span></td>");
                                        _emailTableRow7.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>บาดเจ็บหรือเสียชีวิต (ต่อครั้ง)</span></td>");
                                        _emailTableRow8.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>ความเสียหายในทรัพย์สิน</span></td>");
                                        _emailTableRow9.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>รับผิดชอบส่วนเเรก (ต่ออุบัติเหตุ)</span></td>");
                                        _emailTableRow10.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#FFFFFF'><span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'>รถยนต์สูญหาย เสียหาย ไฟไหม้</span></td>");
                                        _emailTableRow11.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>ความคุ้มครองรถยนต์เอาประกัน</span></td>");
                                        _emailTableRow12.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>รถยนต์สูญหาย หรือไฟไหม้</span></td>");
                                        _emailTableRow13.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>คุ้มครองน้ำท่วม</span></td>");
                                        _emailTableRow14.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#FFFFFF'><span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'>ความคุ้มครองตามเอกสารแนบท้าย</span></td>");
                                        _emailTableRow15.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>อุบัติเหตุส่วนบุคคล (ต่อคน)</span></td>");
                                        _emailTableRow16.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>รักษาพยาบาล (ต่อคน)</span></td>");
                                        _emailTableRow17.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>ประกันตัว ผู้ขับขี่ (ต่อครั้ง)</span></td>");
                                        //_emailTableRow18.Append("<tr><td>พ.ร.บ.</td>");
                                        //_emailTableRow19.Append("<tr><td>เบี้ยประกันภัย</td>");
                                        _emailTableRow20.Append("<tr class='table-txt'><td height='25' bgcolor='#ffd800' class='table-txt' style='font-weight: bold'>ราคาพิเศษจากกล้วยๆ</td>");
                                        int listCount = 0;
                                        if (_dtPremiumList.Rows.Count > 4)
                                            listCount = 4;
                                        else
                                            listCount = _dtPremiumList.Rows.Count;
                                        for (int i = 0; i < 4 && i < _dtPremiumList.Rows.Count; i++)
                                        {
                                            listCount--;
                                            if (_dtPremiumList.Rows[i]["PackageName"].ToString() == "GluayGluay.com Special")
                                                _emailTableRow1.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><img src=\"http://www.gluaygluay.com/images/providercompanylogo/gluaygluay.png\" /></td>");
                                            else
                                                _emailTableRow1.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><img src=\"http://www.gluaygluay.com" + _dtPremiumList.Rows[i]["CompanyLogoServerUrl"].ToString() + "\" /></td>");
                                            if (listCount == 0)
                                                _emailTableRow1.Append("</tr>");

                                            if (_dtPremiumList.Rows[i]["PackageName"].ToString() == "GluayGluay.com Special")
                                                _emailTableRow2.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><span class='table-txt'>GluayGluay.com<br />Special</span></td>");
                                            else
                                                _emailTableRow2.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><span class='table-txt'>" + _dtPremiumList.Rows[i]["CompanyName"].ToString() + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow2.Append("</tr>");
                                            _emailTableRow2_2.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + _insuranceType + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow2_2.Append("</tr>");
                                            _emailTableRow3.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + _garageType + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow3.Append("</tr>");
                                            _emailTableRow4.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + _adddriver + "</td></span>");
                                            if (listCount == 0)
                                                _emailTableRow4.Append("</tr>");
                                            if (listCount == 0)
                                                _emailTableRow5.Append("</tr>");
                                            _emailTableRow6.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["TPBIP"].ToString() != "" && _dtPremiumList.Rows[i]["TPBIP"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["TPBIP"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow6.Append("</tr>");
                                            _emailTableRow7.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["TPBIA"].ToString() != "" && _dtPremiumList.Rows[i]["TPBIA"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["TPBIA"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow7.Append("</tr>");
                                            _emailTableRow8.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["TPPD"].ToString() != "" && _dtPremiumList.Rows[i]["TPPD"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["TPPD"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow8.Append("</tr>");
                                            _emailTableRow9.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["DEC"].ToString() != "" && _dtPremiumList.Rows[i]["DEC"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["DEC"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow9.Append("</tr>");
                                            if (listCount == 0)
                                                _emailTableRow10.Append("</tr>");
                                            _emailTableRow11.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["SumInsured"].ToString() != "" && _dtPremiumList.Rows[i]["SumInsured"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["SumInsured"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow11.Append("</tr>");
                                            _emailTableRow12.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["SumInsured"].ToString() != "" && _dtPremiumList.Rows[i]["SumInsured"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["SumInsured"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow12.Append("</tr>");
                                            _emailTableRow13.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["IsFloodCover"].ToString() == "1" ? (_dtPremiumList.Rows[i]["SumInsured"].ToString() != "" && _dtPremiumList.Rows[i]["SumInsured"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["SumInsured"]).ToString("#,000") : "-") : "0") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow13.Append("</tr>");
                                            if (listCount == 0)
                                                _emailTableRow14.Append("</tr>");
                                            _emailTableRow15.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["PAPassenger"].ToString() != "" && _dtPremiumList.Rows[i]["PAPassenger"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["PAPassenger"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow15.Append("</tr>");
                                            _emailTableRow16.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["MedicalExpense"].ToString() != "" && _dtPremiumList.Rows[i]["MedicalExpense"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["MedicalExpense"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow16.Append("</tr>");
                                            _emailTableRow17.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["PADriver"].ToString() != "" && _dtPremiumList.Rows[i]["PADriver"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["PADriver"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow17.Append("</tr>");
                                            //_emailTableRow18.Append("<td>" + (_dtPremiumList.Rows[i]["TotalCMI"].ToString() != "" && _dtPremiumList.Rows[i]["TotalCMI"].ToString() != "0" ? Convert.ToDouble(_dtPremiumList.Rows[i]["TotalCMI"]).ToString("#,000.00") : "-") + "</td>");
                                            //if (listCount == 0)
                                            //    _emailTableRow18.Append("</tr>");
                                            //_emailTableRow19.Append("<td>" + (_dtPremiumList.Rows[i]["AnnualPremium"].ToString() != "" && _dtPremiumList.Rows[i]["AnnualPremium"].ToString() != "0" ? Convert.ToDouble(_dtPremiumList.Rows[i]["AnnualPremium"]).ToString("#,000.00") : "-") + "</td>");
                                            //if (listCount == 0)
                                            //    _emailTableRow19.Append("</tr>");
                                            _emailTableRow20.Append("<td height='25' bgcolor='#ffd800' class='table-txt' style='font-weight: bold'>" + (_dtPremiumList.Rows[i]["PromotionPrice"].ToString() != "" && _dtPremiumList.Rows[i]["PromotionPrice"].ToString() != "0" ? Convert.ToDouble(_dtPremiumList.Rows[i]["PromotionPrice"]).ToString("#,000.00") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow20.Append("</tr>");
                                        }

                                        _emailTitle = "ข้อมูลประกันรถยนต์สำหรับ" + _dsCarDetail.Tables[0].Rows[0]["MakeModelDesc"].ToString() + " ปี  " + _dsCarDetail.Tables[0].Rows[0]["ManufactYear"].ToString();
                                        /////////////////body////////////////

                                        _emailBody.Append(@"<html>
                                                            <head>
                                                            <meta charset='utf-8'>
                                                            <title>GluayGluay</title>
                                                            <style> 
                                                            body {
	                                                            margin-left: 0px;
	                                                            margin-top: 0px;
	                                                            margin-right: 0px;
	                                                            margin-bottom: 0px;
	                                                            font-family: tahoma, Arial, sans-serif;
	                                                            font-size: 14px;
	                                                            color: #4F4356 ;
	                                                            background-repeat: repeat-x;
	                                                            background-color: #fff;
	                                                            text-decoration: none;
                                                            }
                                                            a:link  {text-decoration:none; color:#0674DB;}
                                                            hr{display: block; height: 1px;
                                                                border: 0; border-top: 1px solid #ccc;
                                                                margin: 1em 0; padding: 0;}
	                                                            .line-height{
	                                                            line-height:20px;}
	                                                            .table-txt{
		                                                            padding-left:5px;}
                                                            </style>
                                                            </head>
                                                            <body>
                                                            <div class='email-containter'>
                                                            <div class='email-banner'></div>
                                                            <div class='email-text'>
                                                            <table width='800' border='0' align='center' cellpadding='0' cellspacing='0'><tr>
                                                            <td height='653' align='left' valign='bottom' bgcolor='#FFFFFF'>
                                                            <table width='800' height='100' border='0' cellspacing='0' cellpadding='0'>
                                                            <tr>
                                                              <td><span class='email-banner'><img src='http://www.gluaygluay.com/images/email-header.jpg' width='800' height='100' alt=''/></span></td>
                                                            </tr>
                                                            </table>
                                                            <table width='800' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                            <tr><td width='672' height='419' align='left' valign='top' bgcolor='#FFFFFF'>
                                                            <table width='790' border='0' cellspacing='0' cellpadding='0'><tr>");

                                        _emailBody.Append("<td width='573' align='left'>");
                                        _emailBody.Append("<p>เรียน <strong>คุณ" + drResrult1["Firstname"].ToString() + " " + drResrult1["Lastname"].ToString() + "</strong>,<br /></p>");
                                        _emailBody.Append("<p style='text-align: justify'>ขอบคุณที่ใช้บริการ GluayGluay.com สำหรับการค้นหาเบี้ยประกันรถยนต์ของคุณปีที่แล้ว<br /><br />");
                                        _emailBody.Append("ข้อมูลที่ส่งมานี้เป็นข้อมูลล่าสุดสำหรับเบี้ยประกันภัยรถยนต์ของคุณในปีนี้หรือคุณสามารถตรวจสอบข้อมูลด้วยเงื่อนไขของคุณออนไลน์โดย ");
                                        _emailBody.Append("<a href=\"http://www.gluaygluay.com/car-insurance/sign-in-auto.aspx?uid=" + drResrult1["RowGuid"].ToString() + "&carid=" + drResrult1["CarID"].ToString() + "\" style=\"font-weight:bold;font-size:13px; text-decoration:none;\"><span>คลิ๊กที่นี่</span></a></p>");

                                        _emailBody.Append("</td><td width='217' valign='top' style='font-weight: normal; font-size: 24px; text-align: right;'>");
                                        _emailBody.Append("<a href=\"tel:022688222\">โทร 02-268-8222</a></td></tr></table>");
                                        _emailBody.Append("<table width='800' border='0' cellspacing='1' cellpadding='1'>");
                                        _emailBody.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#ffd800'>");
                                        _emailBody.Append("<span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'><span style='font-weight: bold'>ข้อมูลสำหรับ : " + _dsCarDetail.Tables[0].Rows[0]["MakeModelDesc"].ToString() + " ปี  " + _dsCarDetail.Tables[0].Rows[0]["ManufactYear"].ToString());
                                        _emailBody.Append("</span></span></td></tr>");

                                        _emailBody.Append( _emailTableRow1.ToString() + _emailTableRow2.ToString() + _emailTableRow2_2.ToString() + _emailTableRow3.ToString() + _emailTableRow4.ToString() + _emailTableRow5.ToString() + _emailTableRow6.ToString() + _emailTableRow7.ToString() + _emailTableRow8.ToString() + _emailTableRow9.ToString() + _emailTableRow10.ToString() + _emailTableRow11.ToString() + _emailTableRow12.ToString() + _emailTableRow13.ToString() + _emailTableRow14.ToString() + _emailTableRow15.ToString() + _emailTableRow16.ToString() + _emailTableRow17.ToString() + _emailTableRow20.ToString() + "</table>");//+ _emailTableRow18.ToString() + _emailTableRow19.ToString()
                                        
                                        //_emailBody.Append("</td></tr><tr><td colspan=\"" + listCount.ToString() + "\" style=\"font-weight:bold;text-align:left;width:630px;\">");
                                        _emailBody.Append("<tr class='table-txt'>");
                                        _emailBody.Append("<td height='40' colspan='5' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: normal'>");
                                        _emailBody.Append("หมายเหตุ:ข้อกำหนดและเงื่อนไขสำหรับราคาพิเศษ");
                                        _emailBody.Append("</td></tr><tr class='table-txt'>");
                                        /////////////////foot////////////////
                                        //_emailBody.Append("<img src=\"http://www.gluaygluay.com/CAPTCHA/CAPTCHAImageHandler.ashx?uid=" + drResrult1["RowGuid"].ToString() + "&carid=" + drResrult1["CarID"].ToString() + "&type=rerunquote\" style=\"display:none\" width=\"0\" height=\"0\">");
                                        //_emailBody.Append("<table border=\"0px\" style=\"font-size:12px; width:630px; text-align:center;\">");
                                        //_emailBody.Append("<tr><td>");
                                        //_emailBody.Append("<table border=\"0px\" style=\"font-size:12px; width:600px; text-align:center;\">");
                                        //_emailBody.Append("<tr><td colspan=\"2\">");
                                        //_emailBody.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"text-align:left;font-family:Arial,Helvetica,sans-serif; font-size:18px; padding-left:50px; width:550px;\"><tr><td>");
                                        _emailBody.Append("<td height='25' colspan='3' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: normal'>");


                                        _emailBody.Append("โปรโมชั่น สำหรับลุกค้าที่ตอบรับภายใน 7 วันนับจากที่ได้รับใบเสนอราคา<br />");
                                        _emailBody.Append("1. บริการรถยก (ค่าบริการ + 15 กิโลแรก)  **สำหรับรถไม่เกิน 15 ปี<br />");
                                        _emailBody.Append("2. ค่าเสียเวลา 1,000 บาท<br />");
                                        _emailBody.Append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;** เคลมเป็นฝ่ายถูกเท่านั้น แจ้งเบิกภายใน 30 วันนับจากวันที่ได้รับใบเคลม , 1 ครั้ง / 1 ปีกรมธรรม์<br />");
                                        _emailBody.Append("3. แจ้งต่อล่วงหน้า  15 วัน รับส่วนลดพิเศษเพิ่มเติม  200 - 1,000 บาท<br />");
                                        _emailBody.Append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;** สำหรับเบี้ยประกันรถยนต์ ราคา 10,000 ขึ้นไป<br /><br />");
                                        _emailBody.Append("<span style=\"color:red; font-size:25px;\">โปรดระวังมิจฉาชีพแอบอ้างเป็นโบรกเกอร์กล้วยๆ<br />หรือแอบอ้างเป็นศูนย์รถหรือได้เบอร์มาจากบริษัทประกันภัย</span><br />");
                                        _emailBody.Append("เจ้าหน้าที่ของเราจะติดต่อท่านด้วยหมายเลข 02 646 9101 - 16 เท่านั้น<br />");
                                        _emailBody.Append("สามารถตรวจสอบข้อมูลเพิ่มเติมได้ที่ ");
                                        _emailBody.Append("<a  style=\"color:rgb(59,89,152);text-decoration:none\" href=\"http://www.gluaygluay.com\">www.GluayGluay.com</a>");
                                        _emailBody.Append("<br />หรือโทร <a href=\"tel:022688222\"><strong>0-2268-8222</strong></a><br /></td>");
                                        _emailBody.Append("<td height='25' colspan='2' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: normal'>&nbsp;</td>");
                                        _emailBody.Append("</tr></table><hr><br />ด้วยความนับถือ,<br />");
                                        _emailBody.Append(@"<p><b>ฝ่ายประสานงาน GluayGluay</b><br />
                                                            บริษัทคอมแพร์เอ็กซ์เพรส นายหน้าประกันภัย(ประเทศไทย) จำกัด<br>
                                                            ใบอนุญาติ คปภ.: ว00016/2553<br>
                                                            ชั้น 42 อาคารยูไนเต็ด เซ็นเตอร์ <br>
                                                            เลขที่ 323 ถนนสีลม แขวงสีลม<br>
                                                            เขตบางรัก กรุงเทพฯ 10500</p>
                                                            <p>โทร : 02-268-8222 <br>
                                                            แฟกซ์ : 02-266-6339 <br>
                                                            <a href='mailto:support@gluaygluay.com'>support@gluaygluay.com</a></p></td>
                                                        </tr></table><hr>");
                                        _emailBody.Append(@"<table width='300' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                            <tr>
                                                              <td width='44' style='text-align: center'><a href='https://www.facebook.com/GGmotor'><img src='http://www.gluaygluay.com/images/icons/fb-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='44' style='text-align: center'><a href='https://plus.google.com/+Gluaygluay/posts'><img src='http://www.gluaygluay.com/images/icons/g-plus-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='44' style='text-align: center'><a href='https://www.youtube.com/channel/UCZ4gMtlXymBHxvcXO2cR1vA'><img src='http://www.gluaygluay.com/images/icons/youtube-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='44' style='text-align: center'><a href='http://iconosquare.com/ggmotor'><img src='http://www.gluaygluay.com/images/icons/instagram-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='43' style='text-align: center'><a href='https://www.linkedin.com/company/-com-gluaygluay-com-'><img src='http://www.gluaygluay.com/images/icons/linkedin-big.png' width='44' height='42' alt=''/></a></td>
                                                            </tr>
                                                            </table>
                                                            <table width='800' align='center' height='100' border='0' cellspacing='0' cellpadding='0' background='http://www.gluaygluay.com/images/email-footer.jpg'>
                                                            <tr>
                                                              <td><p style='text-align: center'><br><br>
                                                                © ");
                                    _emailBody.Append(DateTime.Now.ToString("yyyy",new System.Globalization.CultureInfo("en-US")));
                                    _emailBody.Append(@" gluaygluay.com. All rights reserved.<br>
                                                                  อีเมล์นี้ถูกส่งโดยระบบคอมพิวเตอร์ กรุณาอย่าตอบกลับอีเมล์นี้ </p></td>
                                                            </tr>
                                                            </table>
                                                            </td></tr></table>
                                                            </body></html>");
                                        bool _emailSend = false;
                                        while (!_emailSend && sentAttemptCount < 3)
                                        {
                                            if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemailwithcus, _bccemailth, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                                            {
                                                _step = "Step SendGGMail Customer ID = " + _userId.ToString() + ", CarID = " + _carId.ToString();

                                                _db.ExecuteNonSqlStatement("Update [tblRerunQuoteMail_Log] SET IsSendEmail = 1 Where RefCEUserID = " + _userId.ToString() + " And CarID = " + _carId.ToString() + " And LogId = " + _logId.ToString());


                                                _mailThreeSendCount++;
                                                _emailSend = true;
                                                _bccemailth = "";
                                            }
                                            else
                                            {
                                                sentAttemptCount++;
                                                _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + MyMailManager.GetUTCDateTime().ToString() + "].", EventLogEntryType.Error);
                                                System.Threading.Thread.Sleep(60 * 60 * 1000);
                                            }
                                        }
                                        if (sentAttemptCount < 3)
                                            _mailOneSendCount += 1;
                                        System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                                    }
                                    else
                                    {
                                        _step = "Step No Quote Results Customer ID = " + _userId.ToString() + ", CarID = " + _carId.ToString();
                                        _db.ExecuteNonSqlStatement("Update [tblRerunQuoteMail_Log] SET IsNoQuoteResult = 1 Where RefCEUserID = " + _userId.ToString() + " And CarID = " + _carId.ToString() + " And LogId = " + _logId.ToString());

                                        _noQuotationResultCount++;
                                        //strNoQuotationCarList.Append("<tr><td>" + _carId.ToString() + "</td><td>" + _userId.ToString() + "</td></tr>");
                                        _eventLog.WriteEntry("No quotation result >> Customer ID :" + _userId.ToString() + ", CarID: " + _carId.ToString() + " at  [" + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt") + "].", EventLogEntryType.Warning);
                                    }
                                }
                                else if ((IsLive == "1" && drResrult1["Lang"].ToString() == "en") || (IsLive == "0" && _IsEn == 0))
                                //else if (_IsEn == 0)
                                {
                                    _IsEn = 1;
                                    //English

                                    ///cardetail//////////////CP.InsuranceType, CP.GarageType, CP.VehicleType, CP.SumInsured, CP.NCDPercent, CP.IsClaimLastYear, CP.IsCTPL,"
                                    // + " CP.IsAddDriver, CP.NoOfDriver,  CP.CoverageCommenceDate, CP.Info6, CP.Info7, CP.ModifiedDate, CD.Info3, CP.Info8 "
                                    DataSet _dsCarDetail = new DataSet();
                                    string _strSelect = "SELECT CD.MakeModelDesc, CD.ManufactYear , CP.InsuranceType, CP.GarageType, CP.IsAddDriver"
                                                        + " FROM tblCarPolicy CP INNER JOIN tblCarDetailTH CD ON CD.CarId = CP.CECarId "
                                                        + " WHERE CP.RefCEUserId = " + _userId.ToString() + " AND CP.CECarId= " + _carId.ToString();
                                    SqlDataAdapter _daCar = new SqlDataAdapter(_strSelect, _sqlConn);
                                    _sqlConn.Open();
                                    _daCar.Fill(_dsCarDetail);
                                    _sqlConn.Close();
                                    ///////////////////////////
                                    string _insuranceType = "-";
                                    string _garageType = "";
                                    string _adddriver = "";
                                    if (_dsCarDetail.Tables[0].Rows.Count > 0)
                                    {
                                        _insuranceType = GetClassNameEN(_dsCarDetail.Tables[0].Rows[0]["InsuranceType"].ToString());
                                        _garageType = _dsCarDetail.Tables[0].Rows[0]["GarageType"].ToString() == "2" ? "Dealer" : "Authorized";
                                        _adddriver = _dsCarDetail.Tables[0].Rows[0]["IsAddDriver"].ToString() == "1" ? "Yes" : "No";
                                    }
                                    //StringBuilder _emailTableRow = new StringBuilder();
                                    StringBuilder _emailTableRow1 = new StringBuilder();
                                    StringBuilder _emailTableRow2 = new StringBuilder();
                                    StringBuilder _emailTableRow2_2 = new StringBuilder();
                                    StringBuilder _emailTableRow3 = new StringBuilder();
                                    StringBuilder _emailTableRow4 = new StringBuilder();
                                    StringBuilder _emailTableRow5 = new StringBuilder();
                                    StringBuilder _emailTableRow6 = new StringBuilder();
                                    StringBuilder _emailTableRow7 = new StringBuilder();
                                    StringBuilder _emailTableRow8 = new StringBuilder();
                                    StringBuilder _emailTableRow9 = new StringBuilder();
                                    StringBuilder _emailTableRow10 = new StringBuilder();
                                    StringBuilder _emailTableRow11 = new StringBuilder();
                                    StringBuilder _emailTableRow12 = new StringBuilder();
                                    StringBuilder _emailTableRow13 = new StringBuilder();
                                    StringBuilder _emailTableRow14 = new StringBuilder();
                                    StringBuilder _emailTableRow15 = new StringBuilder();
                                    StringBuilder _emailTableRow16 = new StringBuilder();
                                    StringBuilder _emailTableRow17 = new StringBuilder();
                                    //StringBuilder _emailTableRow18 = new StringBuilder();
                                    //StringBuilder _emailTableRow19 = new StringBuilder();
                                    StringBuilder _emailTableRow20 = new StringBuilder();
                                    if (_dtPremiumList.Rows.Count > 0)
                                    {
                                        _emailTableRow1.Append("<tr class='table-txt'><td height='40' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: bold'>Insurance Company</td>");
                                        _emailTableRow2.Append("<tr><td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>Insurance Company</span></td>");
                                        _emailTableRow2_2.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Insurance Type</span></td>");
                                        _emailTableRow3.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>Garage Type</span></td>");
                                        _emailTableRow4.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Named Drivers</span></td>");
                                        _emailTableRow5.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#FFFFFF'><span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'>Third Party Cover</span></td>");
                                        _emailTableRow6.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Bodily Injury/Death (per person)</span></td>");
                                        _emailTableRow7.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>Liability for Bodily Injury/Death</span></td>");
                                        _emailTableRow8.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Liability for Property Damage</span></td>");
                                        _emailTableRow9.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>Deductible (per accident)</span></td>");
                                        _emailTableRow10.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#FFFFFF'><span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'>Owner Damage Cover</span></td>");
                                        _emailTableRow11.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Own Damage</span></td>");
                                        _emailTableRow12.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>Fire/Theft</span></td>");
                                        _emailTableRow13.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Flood Cover</span></td>");
                                        _emailTableRow14.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#FFFFFF'><span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'>Add. Cover. per Endorsement</span></td>");
                                        _emailTableRow15.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Personal Accident (per person)</span></td>");
                                        _emailTableRow16.Append("<tr><td align='left' bgcolor='#FFFFFF'><span class='table-txt'>Medical Expenses (per person)</span></td>");
                                        _emailTableRow17.Append("<tr><td align='left' bgcolor='#E7E7E7'><span class='table-txt'>Bail Bond (per accident)</span></td>");
                                        //_emailTableRow18.Append("<tr><td>CMI (Compulsory Insurance)</td>");
                                        //_emailTableRow19.Append("<tr><td>VMI (Voluntary Insurance)</td>");
                                        _emailTableRow20.Append("<tr class='table-txt'><td height='25' bgcolor='#ffd800' class='table-txt' style='font-weight: bold'>Special Price(+taxes & duties)</td>");

                                        int listCount = 0;
                                        if (_dtPremiumList.Rows.Count > 4)
                                            listCount = 4;
                                        else
                                            listCount = _dtPremiumList.Rows.Count;
                                        for (int i = 0; i < 4 && i < _dtPremiumList.Rows.Count; i++)
                                        {
                                            listCount--;
                                            if (_dtPremiumList.Rows[i]["PackageName"].ToString() == "GluayGluay.com Special")
                                                _emailTableRow1.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><img src=\"http://www.gluaygluay.com/images/providercompanylogo/gluaygluay.png\" /></td>");
                                            else
                                                _emailTableRow1.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><img src=\"http://www.gluaygluay.com" + _dtPremiumList.Rows[i]["CompanyLogoServerUrl"].ToString() + "\" /></td>");
                                            if (listCount == 0)
                                                _emailTableRow1.Append("</tr>");

                                            if (_dtPremiumList.Rows[i]["PackageName"].ToString() == "GluayGluay.com Special")
                                                _emailTableRow2.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><span class='table-txt'>GluayGluay.com<br />Special</span></td>");
                                            else
                                                _emailTableRow2.Append("<td width='138' height='25' align='center' bgcolor='#FFFFFF' class='table-txt'><span class='table-txt'>" + _dtPremiumList.Rows[i]["CompanyName"].ToString() + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow2.Append("</tr>");
                                            _emailTableRow2_2.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + _insuranceType + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow2_2.Append("</tr>");
                                            _emailTableRow3.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + _garageType + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow3.Append("</tr>");
                                            _emailTableRow4.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + _adddriver + "</td></span>");
                                            if (listCount == 0)
                                                _emailTableRow4.Append("</tr>");
                                            if (listCount == 0)
                                                _emailTableRow5.Append("</tr>");
                                            _emailTableRow6.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["TPBIP"].ToString() != "" && _dtPremiumList.Rows[i]["TPBIP"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["TPBIP"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow6.Append("</tr>");
                                            _emailTableRow7.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["TPBIA"].ToString() != "" && _dtPremiumList.Rows[i]["TPBIA"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["TPBIA"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow7.Append("</tr>");
                                            _emailTableRow8.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["TPPD"].ToString() != "" && _dtPremiumList.Rows[i]["TPPD"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["TPPD"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow8.Append("</tr>");
                                            _emailTableRow9.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["DEC"].ToString() != "" && _dtPremiumList.Rows[i]["DEC"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["DEC"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow9.Append("</tr>");
                                            if (listCount == 0)
                                                _emailTableRow10.Append("</tr>");
                                            _emailTableRow11.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["SumInsured"].ToString() != "" && _dtPremiumList.Rows[i]["SumInsured"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["SumInsured"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow11.Append("</tr>");
                                            _emailTableRow12.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["SumInsured"].ToString() != "" && _dtPremiumList.Rows[i]["SumInsured"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["SumInsured"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow12.Append("</tr>");
                                            _emailTableRow13.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["IsFloodCover"].ToString() == "1" ? (_dtPremiumList.Rows[i]["SumInsured"].ToString() != "" && _dtPremiumList.Rows[i]["SumInsured"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["SumInsured"]).ToString("#,000") : "-") : "0") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow13.Append("</tr>");
                                            if (listCount == 0)
                                                _emailTableRow14.Append("</tr>");
                                            _emailTableRow15.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["PAPassenger"].ToString() != "" && _dtPremiumList.Rows[i]["PAPassenger"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["PAPassenger"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow15.Append("</tr>");
                                            _emailTableRow16.Append("<td height='25' align='left' bgcolor='#FFFFFF'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["MedicalExpense"].ToString() != "" && _dtPremiumList.Rows[i]["MedicalExpense"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["MedicalExpense"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow16.Append("</tr>");
                                            _emailTableRow17.Append("<td height='25' align='left' bgcolor='#E7E7E7'><span class='table-txt'>" + (_dtPremiumList.Rows[i]["PADriver"].ToString() != "" && _dtPremiumList.Rows[i]["PADriver"].ToString() != "0" ? Convert.ToInt32(_dtPremiumList.Rows[i]["PADriver"]).ToString("#,000") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow17.Append("</tr>");
                                            //_emailTableRow18.Append("<td>" + (_dtPremiumList.Rows[i]["TotalCMI"].ToString() != "" && _dtPremiumList.Rows[i]["TotalCMI"].ToString() != "0" ? Convert.ToDouble(_dtPremiumList.Rows[i]["TotalCMI"]).ToString("#,000.00") : "-") + "</td>");
                                            //if (listCount == 0)
                                            //    _emailTableRow18.Append("</tr>");
                                            //_emailTableRow19.Append("<td>" + (_dtPremiumList.Rows[i]["AnnualPremium"].ToString() != "" && _dtPremiumList.Rows[i]["AnnualPremium"].ToString() != "0" ? Convert.ToDouble(_dtPremiumList.Rows[i]["AnnualPremium"]).ToString("#,000.00") : "-") + "</td>");
                                            //if (listCount == 0)
                                            //    _emailTableRow19.Append("</tr>");
                                            _emailTableRow20.Append("<td height='25' bgcolor='#ffd800' class='table-txt' style='font-weight: bold'>" + (_dtPremiumList.Rows[i]["PromotionPrice"].ToString() != "" && _dtPremiumList.Rows[i]["PromotionPrice"].ToString() != "0" ? Convert.ToDouble(_dtPremiumList.Rows[i]["PromotionPrice"]).ToString("#,000.00") : "-") + "</span></td>");
                                            if (listCount == 0)
                                                _emailTableRow20.Append("</tr>");
                                        }

                                        _emailTitle = "Car Insurance Quote for " + _dsCarDetail.Tables[0].Rows[0]["MakeModelDesc"].ToString() + " year " + _dsCarDetail.Tables[0].Rows[0]["ManufactYear"].ToString();
                                        /////////////////body////////////////
                                        _emailBody.Append(@"<html>
                                                            <head>
                                                            <meta charset='utf-8'>
                                                            <title>GluayGluay</title>
                                                            <style> 
                                                            body {
	                                                            margin-left: 0px;
	                                                            margin-top: 0px;
	                                                            margin-right: 0px;
	                                                            margin-bottom: 0px;
	                                                            font-family: tahoma, Arial, sans-serif;
	                                                            font-size: 14px;
	                                                            color: #4F4356 ;
	                                                            background-repeat: repeat-x;
	                                                            background-color: #fff;
	                                                            text-decoration: none;
                                                            }
                                                            a:link  {text-decoration:none; color:#0674DB;}
                                                            hr{display: block; height: 1px;
                                                                border: 0; border-top: 1px solid #ccc;
                                                                margin: 1em 0; padding: 0;}
	                                                            .line-height{
	                                                            line-height:20px;}
	                                                            .table-txt{
		                                                            padding-left:5px;}
                                                            </style>
                                                            </head>
                                                            <body>
                                                            <div class='email-containter'>
                                                            <div class='email-banner'></div>
                                                            <div class='email-text'>
                                                            <table width='800' border='0' align='center' cellpadding='0' cellspacing='0'><tr>
                                                            <td height='653' align='left' valign='bottom' bgcolor='#FFFFFF'>
                                                            <table width='800' height='100' border='0' cellspacing='0' cellpadding='0'>
                                                            <tr>
                                                              <td><span class='email-banner'><img src='http://www.gluaygluay.com/images/email-header.jpg' width='800' height='100' alt=''/></span></td>
                                                            </tr>
                                                            </table>
                                                            <table width='800' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                            <tr><td width='672' height='419' align='left' valign='top' bgcolor='#FFFFFF'>
                                                            <table width='790' border='0' cellspacing='0' cellpadding='0'><tr>");

                                        #region Old
                                        //_emailBody.Append("<table border=\"0px\" cellpadding=\"0\" cellspacing=\"0\" style=\"font-family:Arial,Helvetica,sans-serif;font-size:15px; padding-left:50px;\"><tr>");
                                        //_emailBody.Append("<td colspan=\"2\" style=\"width:380px\">");
                                        //_emailBody.Append("Dear <b>" + drResrult1["Lastname"].ToString() + " " + drResrult1["Firstname"].ToString() + "</b>,<br /><br />");
                                        //_emailBody.Append("Thank you for choosing GluayGluay.com for your Motor Insurance Quote last year!<br /><br />");
                                        //_emailBody.Append("Please find below the latest quote based on your car details. You can also check this quote online, just ");
                                        //_emailBody.Append("<a href=\"http://www.gluaygluay.com/car-insurance/sign-in-auto.aspx?lng=e&uid=" + drResrult1["RowGuid"].ToString() + "&carid=" + drResrult1["CarID"].ToString() + "\" style=\"font-weight:bold;font-size:13px; text-decoration:none;\"><span>click here.</span></a><br />");

                                        //_emailBody.Append("</td><td style=\"vertical-align:top; text-align:right;\"><br /><br />");
                                        //_emailBody.Append("<a href=\"tel:022688222\"><img width=\"200\" alt=\"Call 0-2268-8222\" src=\"http://www.gluaygluay.com/images/banners/hotline_en.png\"/></a>");
                                        //_emailBody.Append("</td></tr><td colspan=\"3\" style=\"font-weight:bold;text-align:left;width:630px;\"");
                                        //_emailBody.Append("<br/><br/>Quote for " + _dsCarDetail.Tables[0].Rows[0]["MakeModelDesc"].ToString() + " year " + _dsCarDetail.Tables[0].Rows[0]["ManufactYear"].ToString());
                                        //_emailBody.Append("</td></tr></table>");
                                        //_emailBody.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"font-family:Arial,Helvetica,sans-serif; padding-left:50px; font-size: 14px; width:630px;\"><tr><td style=\"text-align:center;\">");
                                        //_emailBody.Append("<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" style=\"font-family:Arial,Helvetica,sans-serif; font-size: 12px; width:600px; text-align:left;\">" + _emailTableRow1.ToString() + _emailTableRow2.ToString() + _emailTableRow2_2.ToString() + _emailTableRow3.ToString() + _emailTableRow4.ToString() + _emailTableRow5.ToString() + _emailTableRow6.ToString() + _emailTableRow7.ToString() + _emailTableRow8.ToString() + _emailTableRow9.ToString() + _emailTableRow10.ToString() + _emailTableRow11.ToString() + _emailTableRow12.ToString() + _emailTableRow13.ToString() + _emailTableRow14.ToString() + _emailTableRow15.ToString() + _emailTableRow16.ToString() + _emailTableRow17.ToString() + _emailTableRow20.ToString() + "</table>");//+ _emailTableRow18.ToString() + _emailTableRow19.ToString()
                                        //_emailBody.Append("</td></tr><tr><td colspan=\"" + listCount.ToString() + "\" style=\"font-weight:bold;text-align:left;width:630px;\">");
                                        //_emailBody.Append("Note: Terms and conditions apply for Special Price.");
                                        //_emailBody.Append("</td></tr></table><br/>");
                                        ///////////////////foot////////////////
                                        //_emailBody.Append("<img src=\"http://www.gluaygluay.com/CAPTCHA/CAPTCHAImageHandler.ashx?uid=" + drResrult1["RowGuid"].ToString() + "&carid=" + drResrult1["CarID"].ToString() + "&type=rerunquote\" style=\"display:none\" width=\"0\" height=\"0\">");
                                        //_emailBody.Append("<table border=\"0px\" style=\"font-size:12px; width:630px; text-align:center;\">");
                                        //_emailBody.Append("<tr><td>");
                                        //_emailBody.Append("<table border=\"0px\" style=\"font-size:12px; width:600px; text-align:center;\">");
                                        //_emailBody.Append("<tr><td colspan=\"2\">");
                                        //_emailBody.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"text-align:left;font-family:Arial,Helvetica,sans-serif; font-size:15px; padding-left:50px; width:550px;\"><tr><td>");
                                        //_emailBody.Append("If you need further assistance, visit our website ");
                                        //_emailBody.Append("<a  style=\"color:rgb(59,89,152);text-decoration:none\" href=\"http://www.gluaygluay.com/en\">www.GluayGluay.com</a>");
                                        //_emailBody.Append(" or call us on <a href=\"tel:022688222\"><strong>0-2268-8222</strong></a>.<br /><br />");
                                        //_emailBody.Append("Remember, when you buy we’ll confirm your coverage immediately.<br /><br />");
                                        //_emailBody.Append("Best regards,<br />");
                                        //_emailBody.Append("<b>GluayGluay Support Team</b><br />");
                                        //_emailBody.Append("<a  style=\"text-decoration:none\" href=\"http://www.gluaygluay.com/en\"><img src=\"http://www.gluaygluay.com/maintenance-test/gluaygluay-logo_01.jpg\"/></a>");
                                        //_emailBody.Append("</td></tr></table>");
                                        //_emailBody.Append("</td></tr>");
                                        //_emailBody.Append("<tr><td style=\"text-align: left; width: 500px; padding-left: 27px\"><a href=\"http://www.gluaygluay.com/en\"><img src=\"http://www.gluaygluay.com/images/email/footer_all.png\" title=\"www.gluaygluay.com\" style=\"border:0px;\" /></a></td></tr>");
                                        //_emailBody.Append("<tr>");
                                        //_emailBody.Append("<td style=\"text-align:left; width:500px; padding-left:45px;\"><br />");

                                        //_emailBody.Append("<table border=\"0\" cellspacing=\"5\" width=\"630px\"><tr>");
                                        //_emailBody.Append("<td><img src=\"http://www.gluaygluay.com/images/icon/facebook_16x16.png\" alt=\"\" />&nbsp;&nbsp;Like us on");
                                        //_emailBody.Append("&nbsp;<a href=\"https://www.facebook.com/GGmotor/\">Facebook</a></td>");
                                        //_emailBody.Append("<td>&nbsp;&nbsp;&nbsp;<img src=\"http://www.gluaygluay.com/images/icon/linkedin_16x16.png\" alt=\"\" />&nbsp;&nbsp;Like");
                                        //_emailBody.Append("&nbsp;<a href=\"http://www.linkedin.com/company/-com-gluaygluay-com-\">Linkedin</a></td>");
                                        //_emailBody.Append("<td>&nbsp;&nbsp;&nbsp;<img src=\"http://www.gluaygluay.com/images/icon/gplus_16x16.png\" alt=\"\" />&nbsp;&nbsp;Follow us on");
                                        //_emailBody.Append("&nbsp;<a href=\"https://plus.google.com/u/0/100981796937900198282/posts\">Google+</a></td></tr></table>");
                                        //_emailBody.Append("</td></tr>");
                                        //_emailBody.Append("</table>");
                                        //_emailBody.Append("</td></tr>");
                                        //_emailBody.Append("</table>");
                                        #endregion

                                        _emailBody.Append("<td width='573' align='left'>");
                                        _emailBody.Append("<p>Dear <strong>" + drResrult1["Lastname"].ToString() + " " + drResrult1["Firstname"].ToString() + "</strong>,<br /></p>");
                                        _emailBody.Append("<p style='text-align: justify'>Thank you for choosing GluayGluay.com for your Motor Insurance Quote last year!<br /><br />");
                                        _emailBody.Append("Please find below the latest quote based on your car details. You can also check this quote online, just ");
                                        _emailBody.Append("<a href=\"http://www.gluaygluay.com/car-insurance/sign-in-auto.aspx?lng=e&uid=" + drResrult1["RowGuid"].ToString() + "&carid=" + drResrult1["CarID"].ToString() + "\" style=\"font-weight:bold;font-size:13px; text-decoration:none;\"><span>click here.</span></a></p>");

                                        _emailBody.Append("</td><td width='217' valign='top' style='font-weight: normal; font-size: 24px; text-align: right;'>");
                                        _emailBody.Append("<a href=\"tel:022688222\">Call 02-268-8222</a></td></tr></table>");
                                        _emailBody.Append("<table width='800' border='0' cellspacing='1' cellpadding='1'>");
                                        _emailBody.Append("<tr><td height='40' colspan='5' align='left' bgcolor='#ffd800'>");
                                        _emailBody.Append("<span style='font-size:14px; font-weight:bold; padding-left:5px;padding-bottom:5px;padding-top:5px;'><span style='font-weight: bold'>Quote for " + _dsCarDetail.Tables[0].Rows[0]["MakeModelDesc"].ToString() + " year  " + _dsCarDetail.Tables[0].Rows[0]["ManufactYear"].ToString());
                                        _emailBody.Append("</span></span></td></tr>");

                                        _emailBody.Append(_emailTableRow1.ToString() + _emailTableRow2.ToString() + _emailTableRow2_2.ToString() + _emailTableRow3.ToString() + _emailTableRow4.ToString() + _emailTableRow5.ToString() + _emailTableRow6.ToString() + _emailTableRow7.ToString() + _emailTableRow8.ToString() + _emailTableRow9.ToString() + _emailTableRow10.ToString() + _emailTableRow11.ToString() + _emailTableRow12.ToString() + _emailTableRow13.ToString() + _emailTableRow14.ToString() + _emailTableRow15.ToString() + _emailTableRow16.ToString() + _emailTableRow17.ToString() + _emailTableRow20.ToString() + "</table>");//+ _emailTableRow18.ToString() + _emailTableRow19.ToString()

                                        _emailBody.Append("<tr class='table-txt'>");
                                        _emailBody.Append("<td height='40' colspan='5' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: normal'>");
                                        _emailBody.Append("Note: Terms and conditions apply for Special Price.");
                                        _emailBody.Append("</td></tr><tr class='table-txt'>");
                                        /////////////////foot////////////////
                                        _emailBody.Append("<td height='25' colspan='3' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: normal'>");

                                        _emailBody.Append("Promotion for decision within 7 days after get quotation<br />");
                                        _emailBody.Append("1. Car lift (fee + 15 Km. first).   ** Car not over 15 years<br />");
                                        _emailBody.Append("2. Get 1000 Baht for waste time.<br />");
                                        _emailBody.Append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;** Right claim only and withdraw within 30 days from the date of receipt of claim/1 policy<br />");
                                        _emailBody.Append("3. Get discount more 200-1000 if buy insurance before 15 days notice.<br />");
                                        _emailBody.Append(" ** Only for premiun more than 10,000 Baht<br /><br />");
                                        _emailBody.Append("Be careful impersonation fraud GluayGluay brokers.<br />");
                                        _emailBody.Append("We will only contact you with the number 02 646 9101 - 16.<br />");
                                        _emailBody.Append("If you need further assistance, visit our website ");
                                        _emailBody.Append("<a  style=\"color:rgb(59,89,152);text-decoration:none\" href=\"http://www.gluaygluay.com\">www.GluayGluay.com</a>");
                                        _emailBody.Append("<br />or call us on<a href=\"tel:022688222\"><strong>0-2268-8222</strong></a><br /></td>");
                                        _emailBody.Append("<td height='25' colspan='2' align='left' bgcolor='#FFFFFF' class='table-txt' style='font-weight: normal'>&nbsp;</td>");
                                        _emailBody.Append("</tr></table><hr><br />Best regards,<br />");

                                        _emailBody.Append(@"<p><b>GluayGluay Customer Service Team</b><br />
                                                            CompareXpress Insurance Broker (Thailand) Ltd.<br>
                                                            OIC License number ว00016/2553<br>
                                                            42nd Floor, United Center Building,<br>
                                                            323 Silom Road, Bangrak,<br>
                                                            Bangkok 10500</p>
                                                            <p>Tel : 02-268-8222 <br>
                                                            Fax : 02-266-6339 <br>
                                                            <a href='mailto:support@gluaygluay.com'>support@gluaygluay.com</a></p></td>
                                                        </tr></table><hr>");
                                        _emailBody.Append(@"<table width='300' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                            <tr>
                                                              <td width='44' style='text-align: center'><a href='https://www.facebook.com/GGmotor'><img src='http://www.gluaygluay.com/images/icons/fb-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='44' style='text-align: center'><a href='https://plus.google.com/+Gluaygluay/posts'><img src='http://www.gluaygluay.com/images/icons/g-plus-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='44' style='text-align: center'><a href='https://www.youtube.com/channel/UCZ4gMtlXymBHxvcXO2cR1vA'><img src='http://www.gluaygluay.com/images/icons/youtube-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='44' style='text-align: center'><a href='http://iconosquare.com/ggmotor'><img src='http://www.gluaygluay.com/images/icons/instagram-big.png' width='44' height='42' alt=''/></a></td>
                                                              <td width='43' style='text-align: center'><a href='https://www.linkedin.com/company/-com-gluaygluay-com-'><img src='http://www.gluaygluay.com/images/icons/linkedin-big.png' width='44' height='42' alt=''/></a></td>
                                                            </tr>
                                                            </table>
                                                            <table width='800' align='center' height='100' border='0' cellspacing='0' cellpadding='0' background='http://www.gluaygluay.com/images/email-footer.jpg'>
                                                            <tr>
                                                              <td><p style='text-align: center'><br><br>
                                                                © ");
                                        _emailBody.Append(DateTime.Now.ToString("yyyy", new System.Globalization.CultureInfo("en-US")));
                                        _emailBody.Append(@" gluaygluay.com. All rights reserved.<br>
                                                                  This is a computer-generated email. Please do not reply to this email. </p></td>
                                                            </tr>
                                                            </table>
                                                            </td></tr></table>
                                                            </body></html>");

                                        bool _emailSend = false;
                                        while (!_emailSend && sentAttemptCount < 3)
                                        {
                                            if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemailwithcus, _bccemailen, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                                            {
                                                _step = "Step SendGGMail Customer ID = " + _userId.ToString() + ", CarID = " + _carId.ToString();

                                                _db.ExecuteNonSqlStatement("Update [tblRerunQuoteMail_Log] SET IsSendEmail = 1 Where RefCEUserID = " + _userId.ToString() + " And CarID = " + _carId.ToString() + " And LogId = " + _logId.ToString());

                                                _mailThreeSendCount++;
                                                _emailSend = true;
                                                _bccemailen = "";
                                            }
                                            else
                                            {
                                                sentAttemptCount++;
                                                _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + MyMailManager.GetUTCDateTime().ToString() + "].", EventLogEntryType.Error);
                                                System.Threading.Thread.Sleep(60 * 60 * 1000);
                                            }
                                        }
                                        if (sentAttemptCount < 3)
                                            _mailOneSendCount += 1;
                                        System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                                    }
                                    else
                                    {
                                        _step = "Step No Quote Results Customer ID = " + _userId.ToString() + ", CarID = " + _carId.ToString();
                                        _db.ExecuteNonSqlStatement("Update [tblRerunQuoteMail_Log] SET IsNoQuoteResult = 1 Where RefCEUserID = " + _userId.ToString() + " And CarID = " + _carId.ToString() + " And LogId = " + _logId.ToString());

                                        _noQuotationResultCount++;
                                        //strNoQuotationCarList.Append("<tr><td>" + _carId.ToString() + "</td><td>" + _userId.ToString() + "</td></tr>");
                                        _eventLog.WriteEntry("No quotation result >> Customer ID :" + _userId.ToString() + ", CarID: " + _carId.ToString() + " at  [" + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt") + "].", EventLogEntryType.Warning);
                                    }
                                }

                            }
                        }


                    }

                    if (_dsResult.Tables[0].Rows.Count > 0)
                    {
                        _eventLog.WriteEntry(_serviceTitle + " Finished at [" + MyMailManager.GetUTCDateTime().ToString("dd/MM/yyyy hh:mm:ss tt") + "].", EventLogEntryType.Information);

                        _dbLog = new MyDatabase();
                        _sLogPara = new SortedList();
                        _sLogPara.Add("@ServiceName", "GG_Re_Run_Quote_Service");
                        _sLogPara.Add("@Subject", _serviceTitle + " was finished at " + MyMailManager.GetUTCDateTime().ToString("dd/MM/yyyy hh:mm:ss"));
                        _sLogPara.Add("@EmailCount", _mailThreeSendCount);
                        _sLogPara.Add("@ServiceStatus", 1);
                        _sLogPara.Add("@Status", 1);

                        _dbLog.ExecuteNonSqlSP(_spLogName, _sLogPara);
                        //_toemail = System.Configuration.ConfigurationSettings.AppSettings["ToEmail"].ToString();
                        //_ccemail = System.Configuration.ConfigurationSettings.AppSettings["CcEmail"].ToString();
                        //_bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccEmail"].ToString();
                        //_emailTitle = "<Finished> " + _serviceTitle + " - " + MyMailManager.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt");
                        //_emailBody = new StringBuilder();
                        //_emailBody.Append("Dear All,<br/><br/>");
                        //_emailBody.Append(_serviceTitle + " was finished at " + MyMailManager.GetUTCDateTime().ToString("dd/MMM/yyyy hh:mm:ss tt") + "<br/><br/>");

                        //if (IsSendEmailCustomer == "1")
                        //{
                        //    _emailBody.Append("No. of successfully sent rerun quote mail : <b>" + _mailThreeSendCount.ToString() + "</b><br/><br/>");
                        //}
                        //else
                        //{
                        //    _emailBody.Append("Is send Email Customer : <b>" + IsSendEmailCustomer + "</b><br/><br/>");
                        //}

                        //if (_noQuotationResultCount > 0)
                        //{
                        //    strNoQuotationCarList.Append("</table><br/><br/>");
                        //    _emailBody.Append("<b>No Quotation found list (Count:" + _noQuotationResultCount.ToString() + ")</b> <br/>");
                        //    _emailBody.Append(strNoQuotationCarList);
                        //}

                        //System.Threading.Thread.Sleep(_emailIntervalSec * 1000);

                        //while (!MyMailManager.SendGGMail(_smtpInternalAccount, _smtpInternalAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                        //{
                        //    _eventLog.WriteEntry(_serviceTitle + " - Failed to send FINISH MAIL to GG-Team at [" + MyMailManager.GetUTCDateTime().ToString() + "].", EventLogEntryType.Error);
                        //    System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                        //}
                        //_mailOneSendCount += 1;
                    }
                }

                #endregion
            }
            catch (Exception Ex)
            {
                _eventLog.WriteEntry(_step + ", Message:" + Ex.Message + " >> " + Ex.StackTrace.ToString(), EventLogEntryType.Error);
            }
            finally
            {
                string _mailCountStart = System.Configuration.ConfigurationManager.AppSettings["MailCountStart"].ToString();
                if (_mailOneSendCount > 0 && _mailCountStart == "1")
                {
                    MailCountLog mcl = new MailCountLog();
                    mcl.InsertMailCountLog("Rerun Quote Service", _mailOneSendCount);

                }
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }

                this.Stop();
            }

        }

        private Int64 CalculateOtherClass(Int64 _carId, Int64 _userId, int classCode)
        {
            CarPolicy _carpolicy = new CarPolicy();
            CarPolicyDB _carpolicydb = new CarPolicyDB();
            //CAR POLICY DETAILS
            _carpolicy = _carpolicydb.GetCarPolicy(_carId, _userId);

            //Change to 2+

            if (_carpolicy != null)
            {
                //_carpolicy.Info5 = "UPDATE";
                _carpolicy.InsuranceType = classCode;
                if (_carpolicy.Deductible == -1 || _carpolicy.Deductible == 5000)
                {
                    _carpolicy.Deductible = 0;
                }
                _carpolicy.IsCTPL = false;
                _carpolicy.IsAddDriver = false;
                _carpolicy.NoOfDriver = 0;

                CarDriverDB _cardriverdb = new CarDriverDB();
                _cardriverdb.UpdateAllStatus(_carId, _userId);
                _cardriverdb = null;
                Int64 _policyresult = 0;
                _policyresult = _carpolicydb.Insert(_carpolicy);

                return CalculatePremium();

            }

            _carpolicy = null;
            _carpolicydb = null;
            return 0;
        }

        private string GetClassNameEN(string ClassId)
        {
            switch (ClassId.Trim())
            {
                case "10":
                    return "Class 1";
                case "11":
                    return "Class 1 (Low Cost)";
                case "21":
                    return "Class 2+";
                case "31":
                    return "Class 3+";
                case "30":
                    return "Class 3";
            }

            return "";
        }

        private string GetClassNameTH(string ClassId)
        {
            switch (ClassId.Trim())
            {
                case "10":
                    return "ประกันชั้น 1";
                case "11":
                    return "ประกันชั้น 1 (ประหยัด)";
                case "21":
                    return "ประกันชั้น 2+";
                case "31":
                    return "ประกันชั้น 3+";
                case "30":
                    return "ประกันชั้น 3";
            }

            return "";
        }


        private Int32 CalculatePremium2()
        {
            int _result = 0;
            MyDatabase _db = new MyDatabase();
            SortedList _sPara = new SortedList();

            //Int64 _carId = Convert.ToInt64(Session[General.CarId].ToString());
            //Int64 _userId = 0;

            _sPara.Add("@CarId", _carId);
            _sPara.Add("@RefUserId", _userId);

            _db.ExecuteNonSqlSP("[sprocProviderCarInsuranceClear]", _sPara);

            //select car detail
            _db = new MyDatabase();
            _sPara = new SortedList();
            DataTable _dt = new DataTable("tblModelBody");
            _sPara.Add("@CarId", _carId);
            _sPara.Add("@RefCEUserId", _userId);
            _dt = _db.GetDataTableBySP("[sprocCarDetailTHSelectSingleItem]", _sPara);
            string[] makeName;
            makeName = _dt.Rows[0]["MakeModelDesc"].ToString().Split(' ');

            if (_dt.Rows.Count > 0)
            {
                #region KawnaPackage
                try
                {
                    if (IsKawnaCar(makeName[0]))
                    {
                        DataTable dtPackage = new DataTable();

                        CarPolicy _carpolicy = new CarPolicy();
                        CarPolicyDB _carpolicydb = new CarPolicyDB();
                        _carpolicy = _carpolicydb.GetCarPolicy(_carId, _userId);
                        string strProviderList = "";

                        //if (!Page.IsPostBack || hidReloadKawnaFlag.Value == "1" || ViewState["vwsPackage"] == null)
                        //{
                        string strCarProvince = _carpolicy.ProvinceRegistration == "BKK" ? "1" : "79";
                        string strDriverAge1 = "", strDriverAge2 = "";
                        int DriverAge1 = 0, DriverAge2 = 0;
                        if (_carpolicy.IsAddDriver == true)
                        {
                            CarDriverList _cardriverlist = new CarDriverList();
                            CarDriverDB _cardriverdb = new CarDriverDB();
                            _cardriverlist = _cardriverdb.GetCarDriverList(_carId, _userId);
                            _cardriverdb = null;
                            int _noOfDriver = _carpolicy.NoOfDriver;
                            for (int i = 1; i <= _noOfDriver; i++)
                            {
                                CarDriver _cardriver = _cardriverlist[i - 1];
                                if (i == 1)
                                {
                                    DriverAge1 = MyMailManager.GetUTCDateTime().Year - _cardriver.DOB.Year;
                                }
                                else if (i == 2)
                                {
                                    DriverAge2 = MyMailManager.GetUTCDateTime().Year - _cardriver.DOB.Year;
                                }
                            }
                            _cardriverlist = null;

                            if (DriverAge1 >= 18 && DriverAge1 <= 24)
                                strDriverAge1 = "18";
                            else if (DriverAge1 >= 25 && DriverAge1 <= 35)
                                strDriverAge1 = "25";
                            else if (DriverAge1 >= 36 && DriverAge1 <= 50)
                                strDriverAge1 = "36";
                            else if (DriverAge1 > 50)
                                strDriverAge1 = "50";

                            if (_noOfDriver == 2)
                            {
                                if (DriverAge2 >= 18 && DriverAge2 <= 24)
                                    strDriverAge2 = "18";
                                else if (DriverAge2 >= 25 && DriverAge2 <= 35)
                                    strDriverAge2 = "25";
                                else if (DriverAge2 >= 36 && DriverAge2 <= 50)
                                    strDriverAge2 = "36";
                                else if (DriverAge2 > 50)
                                    strDriverAge2 = "50";
                            }

                        }

                        HttpWebRequest request;

                        string kawnaModel = GetKawnaModelValue(makeName[0], _dt.Rows[0]["CarModel"].ToString() + " " + String.Format("{0:0.0}", (Convert.ToInt32(_dt.Rows[0]["CC"]) * 0.001)) + "cc " + _dt.Rows[0]["Door"].ToString() + " Door");
                        string tun = GetKawnaSingleSumInsured(makeName[0], kawnaModel, _dt.Rows[0]["ManufactYear"].ToString());
                        if (tun == "0") tun = "";
                        string url = _url + "?brand=" + makeName[0].ToString() + "&id_motor=" + kawnaModel.ToString() + "&yearcar=" + _dt.Rows[0]["ManufactYear"].ToString() + "&tun=" + tun + "&Yins=0&dee=0&country=" + strCarProvince + "&rgb=" + strCarProvince + "&paper=no&plantry=&oldinsure=&clame=" + (_carpolicy.IsClaimLastYear == false ? "ไม่มีเครม" : "มีเครม+ไม่มีคู่กรณี") + "&rabu=" + (_carpolicy.IsAddDriver == true ? "yes" : "") + "&old1=" + strDriverAge1 + "&old2=" + strDriverAge2 + "&t=submit";
                        // _url + "?brand=HONDA&id_motor=5&yearcar=2006&tun=500000&Yins=0&dee=0&country=1&rgb=1&paper=no&plantry=&oldinsure=&clame=%E0%B9%84%E0%B8%A1%E0%B9%88%E0%B8%A1%E0%B8%B5%E0%B9%80%E0%B8%84%E0%B8%A3%E0%B8%A1&rabu=no&old1=&old2=&t=submit"; 
                        string responseBody = string.Empty;
                        string requestBody = "";
                        byte[] byteData = UTF8Encoding.UTF8.GetBytes(requestBody.ToString());
                        request = (HttpWebRequest)HttpWebRequest.Create(url);
                        request.ContentType = "application/xml;charset=utf-8";
                        //request.ContentType = "text/xml;charset=utf-8";
                        request.UserAgent = "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; BOIE9;ENUS)";
                        request.Accept = "*/*";
                        request.Method = "POST";
                        request.ContentLength = byteData.Length;
                        System.Net.ServicePointManager.Expect100Continue = false;
                        request.KeepAlive = false;

                        string startTime = DateTime.Now.ToString("HH:mm:ss.ff tt dd/MM/yyyy");
                        DateTime sTime = DateTime.Now;
                        DataTable dtIns = new DataTable();
                        DataSet ds = new DataSet();
                        using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
                        {
                            request.Timeout = 30000;
                            ds.ReadXml(response.GetResponseStream());
                        }
                        dtPackage = ds.Tables["Pre"];
                        DataTable dt = ds.Tables["INS"];
                        dtIns = dt.Clone();
                        dtIns = dt.Copy();

                        // *** Start Join Table ***
                        DataTable dtblResult = dtPackage.Clone();
                        dtblResult.Columns.Add("InsName", typeof(string));
                        dtblResult.Columns.Add("INS_Text", typeof(string));
                        dtblResult.Columns.Add("PRE_IdIns", typeof(string));

                        //get row data
                        var rowData = from row1 in dtPackage.AsEnumerable()
                                      join row2 in dtIns.AsEnumerable()
                                      on row1["PRE_Id"] equals row2["PRE_Id"]
                                      select row1.ItemArray.Concat(row2.ItemArray).ToArray();

                        //Add row data to dtblResult
                        foreach (object[] values in rowData)
                            dtblResult.Rows.Add(values);

                        //Change column name back to original
                        dtblResult.Columns["INS_Text"].ColumnName = "INS";

                        //Remove extra column from result
                        dtblResult.Columns.Remove("PRE_IdIns");
                        dtblResult.Columns.Add("PROPRICE", typeof(Double));
                        //if (ddlClass.SelectedValue == "ป1")
                        //{
                        //foreach (DataRow row in dtblResult.Rows)
                        //{
                        //    row["PROPRICE"] = GetPromotionPrice(Convert.ToDouble(row["PRICE"].ToString()), Convert.ToInt32(row["INS"].ToString()));
                        //}
                        //}

                        dtPackage.Dispose();
                        dtPackage = new DataTable();
                        dtPackage = dtblResult.Clone();
                        dtPackage.Columns["PRICE"].DataType = typeof(string);
                        //dtPackage = dtblResult.Copy();
                        foreach (DataRow row in dtblResult.Rows)
                        {
                            dtPackage.ImportRow(row);
                        }

                        dtblResult.Dispose();
                        dtIns.Dispose();
                        // *** End Join Table ***

                        //ViewState["vwsPackage"] = dtPackage;

                        _db = new MyDatabase();
                        _sPara = new SortedList();
                        DataTable _dtPvdList = new DataTable();
                        _dtPvdList = _db.GetDataTable("SELECT ProviderId FROM tblKawnaProvider WHERE ProviderCompanyId IS NOT NULL");

                        foreach (DataRow dr in _dtPvdList.Rows)
                        {
                            if (strProviderList != null && strProviderList != "")
                                strProviderList += ",";
                            strProviderList += dr["ProviderId"];
                        }
                        //Session["strKawnaProvider"] = strProviderList;
                        //hidReloadKawnaFlag.Value = "";
                        //}
                        //else
                        //{
                        //    dtPackage = (DataTable)ViewState["vwsPackage"];
                        //    strProviderList = Session["strKawnaProvider"].ToString();

                        //}
                        var strExpr = " INS IN(" + strProviderList + ") ";

                        if (_carpolicy.InsuranceType == 10 || _carpolicy.InsuranceType == 11)
                            strExpr += " AND POTYPE = 'ป1' ";
                        else if (_carpolicy.InsuranceType == 21)
                            strExpr += " AND POTYPE = 'ป2+' ";
                        else if (_carpolicy.InsuranceType == 31)
                            strExpr += " AND POTYPE = 'ป3+' ";
                        else if (_carpolicy.InsuranceType == 30)
                            strExpr += " AND POTYPE = 'ป3' ";

                        if (_carpolicy.GarageType > 0 && _carpolicy.InsuranceType != 30)
                        {
                            strExpr += " AND FIX = ";
                            strExpr += _carpolicy.GarageType.ToString() != "1" ? "'ห้าง' " : "'อู่' ";
                        }

                        if (_carpolicy.InsuranceType != 30)
                        {
                            if (_carpolicy.Deductible == 0)
                                strExpr += " AND (ODDD = '' OR ODDD IS NULL)";
                            else if (_carpolicy.Deductible > 0)
                                strExpr += " AND ODDD <> ''";
                        }

                        if (_carpolicy.InsuranceType == 30)
                        {
                            if (_carpolicy.IsCTPL == true)
                                strExpr += " AND (CMI_INCLUDE = 'yes' )";
                            else
                                strExpr += " AND (CMI_INCLUDE <> 'yes' )";
                        }


                        var strSort = " PRICE ASC, TUN ASC ";
                        //dtPackage.DefaultView.Sort = strSort;               
                        DataView dv = new DataView(dtPackage);

                        dv.RowFilter = strExpr;
                        dv.Sort = strSort;

                        var query = (from order in dv.ToTable().AsEnumerable()
                                     group order by order["INS"] into grp
                                     let fewItems = grp.Take(3).ToList()
                                     select fewItems.CopyToDataTable());

                        DataTable boundTable = new DataTable();
                        boundTable = dv.ToTable().Clone();
                        foreach (var rsv in query)
                        {
                            for (int i = 0; i < rsv.Rows.Count; i++)
                                boundTable.ImportRow(rsv.Rows[i]);
                        }
                        //boundTable = dv.ToTable().Copy();
                        foreach (DataRow drc in boundTable.Rows)
                        {
                            _db = new MyDatabase();
                            _sPara = new SortedList();
                            _sPara.Add("FromSource", "W");
                            _sPara.Add("CarId", _carId);
                            _sPara.Add("ProviderId", drc["INS"].ToString() != "" ? Convert.ToInt32(drc["INS"]) : 0);
                            _sPara.Add("PackageId", drc["PACKID"].ToString() != "" ? Convert.ToInt32(drc["PACKID"]) : 0);
                            _sPara.Add("PackageName", drc["PACKNAME"].ToString());
                            _sPara.Add("TotalPremium", drc["PRICE"].ToString() != "" ? Convert.ToDouble(drc["PRICE"]) : 0.0);
                            _sPara.Add("TotalVMI", drc["PRICE"].ToString() != "" ? Convert.ToDouble(drc["PRICE"]) : 0.0);
                            _sPara.Add("IsIncludeCMI", drc["CMI_INCLUDE"].ToString() != "" ? true : false);
                            _sPara.Add("TPPD", drc["TP"].ToString() != "" ? (Convert.ToInt64(drc["TP"].ToString().Replace(",", ""))) : 0);
                            _sPara.Add("TPBIP", drc["BT"].ToString().ToString() != "" ? (Convert.ToInt64(drc["BT"].ToString().Replace(",", ""))) : 0);
                            _sPara.Add("TPBIA", drc["BI"].ToString().ToString() != "" ? (Convert.ToInt64(drc["BI"].ToString().Replace(",", ""))) : 0);
                            _sPara.Add("DEC", drc["ODDD"].ToString().Trim() != "" ? Convert.ToDouble(drc["ODDD"].ToString().Trim()) : 0);
                            _sPara.Add("SumInsured", drc["TUN"].ToString() != "" ? Convert.ToInt64(drc["TUN"].ToString()) : 0);
                            _sPara.Add("FireAndTheft", drc["FIRE"].ToString() != "" ? Convert.ToDouble(drc["FIRE"].ToString()) : 0.0);
                            _sPara.Add("Theft", drc["TUN"].ToString() != "" ? Convert.ToInt64(drc["TUN"].ToString()) : 0);
                            _sPara.Add("PADriver", drc["PA"].ToString() != "" ? (Convert.ToDouble(drc["PA"].ToString().Replace(",", ""))) : 0.0);
                            _sPara.Add("PAPassenger", drc["PA"].ToString() != "" ? (Convert.ToDouble(drc["PA"].ToString().Replace(",", ""))) : 0.0);
                            _sPara.Add("MED", drc["ME"].ToString() != "" ? (Convert.ToDouble(drc["ME"].ToString().Replace(",", ""))) : 0.0);
                            _sPara.Add("BailBond", drc["BB"].ToString() != "" ? (Convert.ToDouble(drc["BB"].ToString().Replace(",", ""))) : 0.0);
                            _sPara.Add("RefUserId", Convert.ToDouble(_userId));
                            _sPara.Add("IsSpecialOffer", 0);
                            _sPara.Add("IsFloodCover", drc["FLOOD"].ToString().Trim() != "" ? 1 : 0);
                            _sPara.Add("GarageType", drc["FIX"].ToString().Trim() != "" && drc["FIX"].ToString().Trim() == "ห้าง" ? 1 : 0);
                            _sPara.Add("CarType", _dt.Rows[0]["Body"].ToString());
                            _sPara.Add("CarDoors", _dt.Rows[0]["Door"].ToString() != "" ? Convert.ToInt32(_dt.Rows[0]["Door"].ToString()) : 4);
                            _sPara.Add("@PeopleCovered", drc["P"].ToString().Trim() != "" ? drc["P"].ToString().Trim() : "0");
                            _result = _db.ExecuteNonSqlSP("[sprocKawnaSaveResultPackages]", _sPara);

                        }
                        return _result;

                    }
                }
                catch { }

                #endregion
                //else
                //{
                _db = new MyDatabase();
                _sPara = new SortedList();
                _sPara.Add("@CarId", _carId);
                _sPara.Add("@RefUserId", _userId);

                BKIDetailDB bki = new BKIDetailDB();

                _db.ExecuteNonSqlSP("[sprocProviderCarInsuranceClear]", _sPara);

                try
                {
                    bki.BKICalculation(_carId, _userId);
                }
                catch
                {
                }

                _result = _db.ExecuteNonSqlSP("[sprocProviderCarInsuranceCalculation]", _sPara);

                _result = 0;

                object _objResult;
                _objResult = _db.ExecuteScalarSqlStatement("Select Count(CITempResultID) from tblCarInsuranceTempResultTH Where CarId = " + _carId.ToString() + " And RefCEUserID = " + _userId.ToString());

                if (_objResult != null)
                {
                    int.TryParse(_objResult.ToString(), out _result);
                }

                return _result;
                //}
            }
            return 0;
        }

        private Int32 CalculatePremium()
        {
            MyDatabase _db = new MyDatabase();

            SortedList _sPara = new SortedList();
            _sPara.Add("@CarId", _carId);
            BKIDetailDB bki = new BKIDetailDB();
            _sPara.Add("@RefUserId", _userId);

            Int32 _result = 0;


            _db.ExecuteNonSqlSP("[sprocProviderCarInsuranceClear]", _sPara);

            try
            {
                bki.BKICalculation(_carId, _userId);
            }
            catch
            {
            }

            _result = _db.ExecuteNonSqlSP("[sprocProviderCarInsuranceCalculation]", _sPara);

            _result = 0;

            object _objResult;
            _objResult = _db.ExecuteScalarSqlStatement("Select Count(CITempResultID) from tblCarInsuranceTempResultTH Where CarId = " + _carId.ToString() + " And RefCEUserID = " + _userId.ToString());

            if (_objResult != null)
            {
                int.TryParse(_objResult.ToString(), out _result);
            }

            return _result;
        }

        #region kawna
        private string _url = System.Configuration.ConfigurationManager.AppSettings["KawnaAPIURL"].ToString();
        DataSet dsBrand = new DataSet();
        private bool IsKawnaCar(string makeName)
        {

            MyDatabase _db = new MyDatabase();
            object _result = null;
            Int64 _retValue = 0;

            _result = _db.ExecuteScalarSqlStatement("select COUNT(CarModel)from tblKawnaCar WHERE  CarMake = '" + makeName + "' ");
            if (_result != null)
            {
                _retValue = Convert.ToInt64(_result);
            }
            return _retValue > 0;
            //try
            //{
            //HttpWebRequest request;
            ////HttpWebResponse hwResponse;
            //string url = _url + "?t=b";
            //string responseBody = string.Empty;
            //string requestBody = "";
            //byte[] byteData = UTF8Encoding.UTF8.GetBytes(requestBody.ToString());
            //request = (HttpWebRequest)HttpWebRequest.Create(url);
            //request.ContentType = "application/xml;charset=utf-8";
            ////request.ContentType = "text/xml;charset=utf-8";
            //request.UserAgent = "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; BOIE9;ENUS)";
            //request.Accept = "*/*";
            //request.Method = "POST";
            //request.ContentLength = byteData.Length;
            //using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
            //{
            //    dsBrand.ReadXml(response.GetResponseStream());
            //}

            //foreach (DataRow dr in dsBrand.Tables[0].Rows)
            //{
            //    if (dr["B_Text"].ToString().ToLower() == makeName.ToLower())
            //        return true;
            //}
            //}
            //catch
            //{
            //    return false;
            //}
            //return false;
        }

        DataSet dsModel = new DataSet();
        private string GetKawnaModelValue(string brandName, string modelName)
        {
            try
            {
                HttpWebRequest request;
                string url = _url + "?t=m&b=" + brandName;
                string responseBody = string.Empty;
                string requestBody = "";
                byte[] byteData = UTF8Encoding.UTF8.GetBytes(requestBody.ToString());
                request = (HttpWebRequest)HttpWebRequest.Create(url);
                request.ContentType = "application/xml;charset=utf-8";
                //request.ContentType = "text/xml;charset=utf-8";
                request.UserAgent = "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; BOIE9;ENUS)";
                request.Accept = "*/*";
                request.Method = "POST";
                request.ContentLength = byteData.Length;
                System.Net.ServicePointManager.Expect100Continue = false;
                request.KeepAlive = false;
                DataSet dsM = new DataSet();
                using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
                {
                    dsM.ReadXml(response.GetResponseStream());
                }
                foreach (DataRow dr in dsM.Tables[0].Rows)
                {
                    if (dr["M_Text"].ToString().ToLower() == modelName.ToLower())
                        return dr["value"].ToString();
                }
            }
            catch { }
            MyDatabase _db = new MyDatabase();
            DataTable dtKawnaCar = new DataTable();
            dtKawnaCar = _db.GetDataTable("select Distinct CarModelID from tblKawnaCar WHERE CarMake = '" + brandName + "' AND CarModel = '" + modelName + "' ");
            if (dtKawnaCar.Rows.Count > 0)
                return dtKawnaCar.Rows[0]["CarModelID"].ToString();
            return "";
        }
        /// <summary>
        /// Get Minimum Kawna SumInsured
        /// </summary>
        /// <param name="cmake"></param>
        /// <param name="cmodel"></param>
        /// <param name="cyear"></param>
        /// <returns></returns>
        private string GetKawnaSingleSumInsured(string cmake, string cmodel, string cyear)
        {
            if (cmake == "" || cmodel == "" || cyear == "")
                return "";

            try
            {
                HttpWebRequest request;
                string url = _url + "?t=t&b=" + cmake.Trim() + "&m=" + cmodel.Trim() + "&y=" + cyear.Trim();
                string responseBody = string.Empty;
                string requestBody = "";
                byte[] byteData = UTF8Encoding.UTF8.GetBytes(requestBody.ToString());
                request = (HttpWebRequest)HttpWebRequest.Create(url);
                request.ContentType = "application/xml;charset=utf-8";
                //request.ContentType = "text/xml;charset=utf-8";
                request.UserAgent = "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; BOIE9;ENUS)";
                request.Accept = "*/*";
                request.Method = "POST";
                request.ContentLength = byteData.Length;
                System.Net.ServicePointManager.Expect100Continue = false;
                request.KeepAlive = false;

                DataSet dsS = new DataSet();
                using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
                {
                    request.Timeout = 30000;
                    dsS.ReadXml(response.GetResponseStream());
                }

                if (dsS.Tables[0].Rows.Count == 1 && dsS.Tables[0].Rows[0][0].ToString() == "ERR4")
                {
                    // Alert("ไม่พบทุน ของรถรุ่น " +cmodel + " ในปี " + cyear);
                }
                else
                {
                    return dsS.Tables[0].Rows[0][0].ToString();
                }
            }
            catch { }

            MyDatabase _db = new MyDatabase();
            DataTable dtKawnaCar = new DataTable();
            dtKawnaCar = _db.GetDataTable("select SumInsured from tblKawnaCar WHERE CarMake = '" + cmake + "' AND CarModelID = " + cmodel + " And Year = " + cyear + " ");
            if (dtKawnaCar.Rows.Count > 0)
                return dtKawnaCar.Rows[0]["SumInsured"].ToString();
            return "";
        }

        private double GetPromotionPrice(double price, int knInsurerId)
        {

            double _ret = 0;
            if (knInsurerId != 61 && knInsurerId != 2 && price >= 5000.00)
            {
                try
                {
                    double netPremium = Math.Floor(Math.Round(price / 1.07, 0) / 1.004);
                    double discount = netPremium * 5 / 100;
                    double duty = Math.Ceiling(netPremium * 0.004);
                    double vat = (netPremium + duty) * 0.07;
                    _ret = netPremium - discount + duty + vat;
                }
                catch { }
            }
            return _ret;

        }

        #endregion

    }
}